const db = require('../../lib/conn_oracle')

//获取线路站点信息
exports.getLinesInfo = async (req,res) => {
    let resDatas
    let status
    let msg
    let sql = `select *
               from c##wxw.location
               where cust_linenum in ('L03','L3B')
               order by location`
    try{
        let lines = await db.search2(sql)
        status =  lines.errorNum ? 0 : 1
        msg = lines.errorNum? "查询失败" : "查询成功"
        resDatas = {lines}
        res.json({
            status,
            msg,
            resDatas
        })
    }catch(err){
        res.json({
            status:0,
            msg:"查询失败",
            resDatas:{err}
        })
    }

}

//获取专业子系统信息
exports.getSpecialtiesInfo = async (req,res) => {
    let resDatas
    let status
    let msg
    let specSql = `select *
               from c##wxw.specialty
               where cust_specnum in ('FAS','GQT')`
    let subSql = `select *
               from c##wxw.subsys
               where cust_specnum in ('FAS','GQT')
               order by cust_subsys`
    try{
        let specialties = await db.search2(specSql)
        let subsys = await db.search2(subSql)
        status =  (specialties.errorNum || subsys.errorNum) ? 0 : 1
        msg = (specialties.errorNum || subsys.errorNum) ? "查询失败" : "查询成功" 
        resDatas = {specialties,subsys}
        res.json({
            status,
            msg,
            resDatas
        })
    }catch(err){
        res.json({
            status:0,
            msg:"查询失败",
            resDatas:{err}
        })
    } 
}

//获取设备信息
exports.getDeviceInfo = async (req, res) => {
    // console.log(req.body)
    let {form} = req.body
    let {lines,stations,specialties,subsys,status,description} = form
    //#region 
    // console.log(lines)
    // console.log(stations)
    // console.log(specialties)
    // console.log(subsys)
    // console.log(status)
    // console.log(description.length)
    //#endregion
    let resDatas
    let msg
    let resStatus
    let deviceDatas = []
    // console.log(deviceDatas)
    //线路条件
    let linesWhere
    if(lines && lines.length > 1){
        linesWhere = `and cust_linenum in (`
        for (let i = 0; i < lines.length; i++)
        linesWhere += (i > 0) ? ", :" + i : ":" + i
        linesWhere += ")"
        deviceDatas.push(...lines)
    }else if(lines && lines.length === 1){
        linesWhere = `and cust_linenum = :lines` 
        deviceDatas.push(...lines)
    }else{linesWhere = ""}
    //站点条件
    let stationsWhere
    if(stations && stations.length > 1){
        stationsWhere = `and location in (`
        for (let i = 0; i < stations.length; i++)
        stationsWhere += (i > 0) ? ", :" + i : ":" + i 
        stationsWhere += ")"
        deviceDatas.push(...stations)
    }else if(stations && stations.length === 1){
        stationsWhere = `and location = :stations` 
        deviceDatas.push(...stations)
    }else{stationsWhere = ""}
    //专业条件
    let specialtiesWhere
    if(specialties && specialties.length > 1){
        specialtiesWhere = `and cust_specnum in (`
        for (let i = 0; i < specialties.length; i++)
        specialtiesWhere += (i > 0)? ", :" + i : ":" + i
        specialtiesWhere += ")"  
        deviceDatas.push(...specialties)
    }else if(specialties && specialties.length === 1){
        specialtiesWhere = `and cust_specnum = :specialties`
        deviceDatas.push(...specialties)
    }else{specialtiesWhere = ""}
    //子系统条件
    let subsysWhere
    if(subsys && subsys.length > 1){
        subsysWhere = `and cust_subsys in (`
        for (let i = 0; i < subsys.length; i++)
        subsysWhere += (i > 0)? ", :" + i : ":" + i  
        subsysWhere += ")"
        deviceDatas.push(...subsys)
    }else if(subsys && subsys.length === 1){
        subsysWhere = `and cust_subsys = :subsys`
        deviceDatas.push(...subsys)
    }else{subsysWhere = ""}
    //状态条件
    let statusWhere 
    if(status.length > 0){
        statusWhere = `and status = :status`
        deviceDatas.push(status)
    }else{statusWhere = ""}
    //描述条件
    let descLike  
    let descWhere 
    if(description.length > 0){
        descLike = `%${description}%`
        descWhere = `and description like :descLike`
        deviceDatas.push(descLike)
    }else{
        descLike = ""
        descWhere = ""
    }
    //查询SQL
    let deviceSql = `select *
                    from c##wxw.device
                    where 1 = 1
                    ${linesWhere}
                    ${stationsWhere}
                    ${specialtiesWhere}
                    ${subsysWhere}
                    ${statusWhere}
                    ${descWhere}
                    order by devicenum
                    `
    //#region 
    // console.log(linesWhere)
    // console.log(stationsWhere)
    // console.log(specialtiesWhere)
    // console.log(subsysWhere)
    // console.log(statusWhere)
    // console.log(descWhere)
    // console.log(deviceSql)
    // console.log(deviceDatas)
    //#endregion
    
    try{
        let deviceInfo = deviceDatas.length ? await db.search(deviceSql,deviceDatas) : await db.search2(deviceSql)        
        resDatas = {deviceInfo}
        msg = resDatas.deviceInfo.errorNum ? "查询失败" : "查询成功"
        resStatus = resDatas.deviceInfo.errorNum? 0 : 1
        res.json({
            status:resStatus,
            msg,
            resDatas 
        })
    }catch(err){
        res.json({
            status:0,
            msg:"查询失败",
            resDatas:{err}
        }) 
    }

}

//编辑设备信息
exports.editDeviceInfo = async (req,res) => {
    // console.log(req.body)
    let { type,form } = req.body
    let { ID,DESCRIPTION,AREA,AREA2,STATUS,CUST_LINENUM,LOCATION,
        CUST_SPECNUM,CUST_SUBSYS,CUST_USETEAM,ISJIANCE,JCDATE,JCLAST,
        DEVICENUM,SBDESC,REMARK
    } = form 
    let resDatas
    let msg
    let msgType
    let resStatus
    let editSql 
    let editDatas = []
    if(type === 'add' || type === 'copy'){
        msgType = "新增"
        editDatas = [DESCRIPTION,AREA,AREA2,STATUS,CUST_LINENUM,LOCATION,
            CUST_SPECNUM,CUST_SUBSYS,CUST_USETEAM,ISJIANCE,JCDATE,JCLAST,
            DEVICENUM,SBDESC,REMARK]
        editSql = `insert into c##wxw.device(id,description,area,area2,status,cust_linenum,location,cust_specnum,cust_subsys,cust_useteam,isjiance,jcdate,jclast,devicenum,sbdesc,remark)
                    values('',:DESCRIPTION,:AREA,:AREA2,:STATUS,:CUST_LINENUM,:LOCATION,:CUST_SPECNUM,:CUST_SUBSYS,:CUST_USETEAM,:ISJIANCE
                    ,to_timestamp(:JCDATE,'yyyy-mm-dd hh24:mi:ss.ff'),to_timestamp(:JCLAST,'yyyy-mm-dd hh24:mi:ss.ff'),:DEVICENUM,:SBDESC,:REMARK)
                    ` 
        // console.log(editDatas)
        // console.log(editSql)
    }
    if(type === 'update'){
        msgType = "更新"
        editDatas = [DESCRIPTION,AREA,AREA2,STATUS,CUST_LINENUM,LOCATION,
            CUST_SPECNUM,CUST_SUBSYS,CUST_USETEAM,ISJIANCE,JCDATE,JCLAST,
            DEVICENUM,SBDESC,REMARK,ID]
        editSql = `update c##wxw.device
                    set description = :DESCRIPTION,
                    area = :AREA,
                    area2 = :AREA2,
                    status = :STATUS,
                    cust_linenum = :CUST_LINENUM,
                    location = :LOCATION,
                    cust_specnum = :CUST_SPECNUM,
                    cust_subsys = :CUST_SUBSYS,
                    cust_useteam = :CUST_USETEAM,
                    isjiance = :ISJIANCE,
                    jcdate = to_timestamp(:JCDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                    jclast = to_timestamp(:JCLAST,'yyyy-mm-dd hh24:mi:ss.ff'),
                    devicenum = :DEVICENUM,
                    sbdesc = :SBDESC,
                    remark = :REMARK                    
                    where id = :ID`
    }
    if(type === 'delete'){
        msgType = "删除"
        editDatas= [ID]
        editSql = `delete from c##wxw.device where id = :ID` 
    }
    try{
        let newDevice = await db.writeTab(editSql,editDatas)
        // console.log(newDevice)
        resDatas = {newDevice}
        msg = !resDatas.newDevice.errorNum ? `${msgType}成功` : `${msgType}失败`
        resStatus = !resDatas.newDevice.errorNum ? 1 : 0
        res.json({
            status:resStatus,
            msg,
            resDatas
        })
    }catch(err){
        // console.log(err)
        res.json({
            status:0,
            msg:`${msgType}失败`,
            resDatas:{err}
        })
    }
}

//批量编辑设备信息
exports.editManyDeviceInfo = async (req,res) => {
    // console.log(req.body)
    let { type,form } = req.body  
    console.log(type)
    console.log(form)
    let resDatas
    let msg
    let msgType
    let resStatus
    let editSql
    let options = {
        autoCommit:true,
    }
    if(type === 'batchDel'){
        msgType = "批量删除"
        editSql = `delete from c##wxw.device where id = :ID`
    }
    if(type === 'batchAdd'){
        msgType = "批量新增"
        editSql = `insert into c##wxw.device(id,description,area,area2,status,cust_linenum,location,
                                            cust_specnum,cust_subsys,cust_useteam,
                                            isjiance,jcdate,jclast,devicenum,sbdesc,remark)
                                    values(:ID,:DESCRIPTION,:AREA,:AREA2,:STATUS,:CUST_LINENUM,:LOCATION,
                                            :CUST_SPECNUM,:CUST_SUBSYS,:CUST_USETEAM,
                                            :ISJIANCE,to_timestamp(:JCDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                                            to_timestamp(:JCLAST,'yyyy-mm-dd hh24:mi:ss.ff'),
                                            :DEVICENUM,:SBDESC,:REMARK)
                                            `
    }
    if(type === 'batchUpdate'){
        msgType = "批量更新"
        editSql = `update c##wxw.device
                    set description = :DESCRIPTION,
                    area = :AREA,
                    area2 = :AREA2, 
                    status = :STATUS,
                    cust_linenum = :CUST_LINENUM,
                    location = :LOCATION,
                    cust_specnum = :CUST_SPECNUM,
                    cust_subsys = :CUST_SUBSYS,
                    cust_useteam = :CUST_USETEAM,
                    isjiance = :ISJIANCE,
                    jcdate = to_timestamp(:JCDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                    jclast = to_timestamp(:JCLAST,'yyyy-mm-dd hh24:mi:ss.ff'),
                    devicenum = :DEVICENUM,
                    sbdesc = :SBDESC,
                    remark = :REMARK
                    where id = :ID`
    }
    try{
        let newDevice = await db.writeManyTab(editSql,form,options)
        // console.log(newDevice)
        resDatas = {newDevice}
        msg =!resDatas.newDevice.errorNum ? `${msgType}成功` : `${msgType}失败`
        resStatus =!resDatas.newDevice.errorNum? 1 : 0
        res.json({
            status:resStatus,
            msg,
            resDatas
        })
    }catch(err){
        // console.log(err) 
        res.json({
            status:0,
            msg:`${msgType}失败`,
            resDatas:{err}  
        }) 
    }
}

//生成检测计划
exports.createPlan = async (req,res) => {
    // console.log(req.body)
    let { type,form } = req.body
    let {STATUS,ISJIANCE,JCDATE,DEVICENUM} = form
    let resDatas
    let msg
    let msgType
    let resStatus
    let editDatas = []
    let editSql 
    
    if(type === 'createPlan' ){
        msgType = "生成计划"
        if(STATUS === '不活动' || ISJIANCE !== 'Y' ){
            res.json({
                status:0,
                msg:`${msgType}失败,设备状态为不活动或未检测，无法生成计划`,
            })
        }else{
            editDatas = [JCDATE,STATUS,DEVICENUM]
            editSql = `insert into c##wxw.xfjiance(description,area,area2,
                            cust_linenum,cust_specnum,spec_desc,cust_subsys,sub_desc,
                            location,persongroup,pgname,schedfinish,devicenum,sbdesc,status)
                        select d.description,d.area,d.area2
                            ,d.cust_linenum,d.cust_specnum,s.spec_desc
                            ,d.cust_subsys,ss.sub_desc
                            ,d.location,d.cust_useteam,pg.pgname
                            ,to_timestamp(:JCDATE,'yyyy-mm-dd hh24:mi:ss.ff') as schedfinish
                            ,d.devicenum
                            ,d.sbdesc
                            ,case when status = '活动' then 1 else 0 end as status
                        from c##wxw.device d
                        inner join c##wxw.specialty s on d.cust_specnum = s.cust_specnum
                        inner join c##wxw.subsys ss on d.cust_subsys = ss.cust_subsys
                        inner join c##wxw.persongroup pg on d.cust_useteam = pg.persongroup
                        where isjiance = 'Y'
                          and status = :STATUS
                          and devicenum = :DEVICENUM
                        `
            try{
                let newPlan = await db.writeTab(editSql,editDatas)
                console.log(newPlan)
                resDatas = {newPlan}
                msg =!resDatas.newPlan.errorNum? `${msgType}成功` : `${msgType}失败`
                resStatus =!resDatas.newPlan.errorNum? 1 : 0
                res.json({
                    status:resStatus,
                    msg,
                    resDatas
                })
            }catch(err){
                console.log(err)
                res.json({
                    status:0,
                    msg:`${msgType}失败`,
                    resDatas:{err}
                })
            }
        }
    }
}

//批量生成检测计划
exports.batchCreatePlan = async (req,res) => {
    let { type,form } = req.body
    console.log(form);
    
    let resDatas
    let msg
    let msgType
    let resStatus
    let errDatas
    let editDatas = []
    let editSql
    let options = {
        autoCommit:true,
    }       
    if(type === 'batchCreatePlans'){
        errDatas = []
        msgType = "批量生成计划"
        form.forEach((item,index) => {
            if(item.STATUS === '不活动' || item.ISJIANCE!== 'Y' ){
                errDatas.push({item,msg:`第${index}行,${item.DEVICENUM}设备状态为不活动或未检测，无法生成计划，${msgType}失败`})
            }else{
                let editData = []
                editData.push(item.JCDATE,item.STATUS,item.DEVICENUM)
                editDatas.push(editData)
            }
        })
        console.log(errDatas);
        console.log(editDatas);
        if(errDatas.length > 0){
            res.json({
                status:0,
                msg:`${msgType}失败`,
                resDatas:{errDatas}
            })
        }else{
            editSql = `insert into c##wxw.xfjiance(description,area,area2,
                            cust_linenum,cust_specnum,spec_desc,cust_subsys,sub_desc,
                            location,persongroup,pgname,schedfinish,devicenum,sbdesc,status)
                        select d.description,d.area,d.area2
                           ,d.cust_linenum,d.cust_specnum,s.spec_desc
                           ,d.cust_subsys,ss.sub_desc
                          ,d.location,d.cust_useteam,pg.pgname
                          ,to_timestamp(:JCDATE,'yyyy-mm-dd hh24:mi:ss.ff') as schedfinish
                          ,d.devicenum
                          ,d.sbdesc
                          ,case when status = '活动' then 1 else 0 end as status
                        from c##wxw.device d
                        inner join c##wxw.specialty s on d.cust_specnum = s.cust_specnum
                        inner join c##wxw.subsys ss on d.cust_subsys = ss.cust_subsys
                        inner join c##wxw.persongroup pg on d.cust_useteam = pg.persongroup
                        where isjiance = 'Y'
                          and status = :STATUS
                          and devicenum = :DEVICENUM
                        `
            try{
                let newPlan = await db.writeManyTab(editSql,editDatas,options)  
                resDatas = {newPlan}    
                msg =!resDatas.newPlan.errorNum? `${msgType}成功` : `${msgType}失败`
                resStatus =!resDatas.newPlan.errorNum? 1 : 0
                res.json({
                    status:resStatus,
                    msg,
                    resDatas
                })
            }catch(err){
                console.log(err)
                res.json({
                    status:0,
                    msg:`${msgType}失败`,
                    resDatas:{err}
                })
            }
        }
        
    }
}